package dal;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collection;

import com.google.gson.JsonIOException;
import com.google.gson.JsonSyntaxException;

import dal.datastore.Database;
import dal.datastore.DatabaseData;
import dal.datastore.PhysicalDataManipulation;
import dal.datastore.Table;
import dal.datastore.TableData;
import dto.ColumnDTO;
import dto.RowDTO;

;

/**
 * Database manipulations in here.
 * 
 * @author Stanley
 * 
 */
public class JMLDatabase {
	private PhysicalDataManipulation pdm;
	private Database db;

	public JMLDatabase() {
		super();
		pdm = new PhysicalDataManipulation();
		this.db = null;
	}

	/**
	 * get data and all things about an existing database
	 * 
	 * @param dbName
	 *            string
	 * @return null
	 */
	public JMLDatabase(String dbName) {
		super();
		pdm = new PhysicalDataManipulation();
		this.db = new Database(dbName);
	}

	public boolean createDatabase(String dbname) throws Exception {
		if (!dbname.isEmpty()) {
			// 1. Create database
			Database db = new Database(dbname);
			// 2. add database to configuration file
			pdm.createDatabase(db);
			return true;

		} else {
			throw new Exception("Database name is null");
		}
	}

	/**
	 * Create table with table name
	 * 
	 * @param dbname
	 * @param tableName
	 * @return true if create succeed.
	 * @throws Exception
	 */
	public boolean createTable(String dbname, String tableName)
			throws Exception {
		if (dbname.isEmpty() || tableName.isEmpty()) {
			throw new Exception("Database name or table name is null");
		}
		return true;
	}

	/**
	 * Get all database name.
	 * 
	 * @return ArrayList
	 */
	public ArrayList<String> getAllDatabaseName() {
		Collection<Object> collection;
		try {
			collection = pdm.getAllDatabases();
		} catch (Exception e) {
			return null;
		}
		ArrayList<String> result = new ArrayList<String>();
		for (Object object : collection) {
			result.add(((Database) object).getName());
		}
		return result;
	}

	/**
	 * Drop a database
	 * 
	 * @param dbname
	 * @return true if drop successful and vice versa.
	 * @throws Exception
	 */
	public boolean dropDatabase(String dbname) throws Exception {
		if (!dbname.isEmpty()) {
			// 1. Create database
			Database db = new Database(dbname);
			// 2. add database to configuration file
			pdm.dropDatabase(db);
			return true;
		} else {
			throw new Exception("Database name is null");
		}
	}

	/**
	 * @param dbname
	 *            database name which is contains table.
	 * @param tableName
	 *            table name
	 * @param columns
	 *            a list of columns.
	 * @return true if create successful and vice versa.
	 * @throws Exception
	 */
	public boolean createTable(String dbname, String tableName,
			ArrayList<ColumnDTO> columns) throws Exception {
		if (dbname != null && tableName != null) {
			ArrayList<String> tableNames = getAllTableName(dbname);
			if (tableNames != null && tableNames.contains(tableName)) {
				throw new Exception("Table " + tableName + " is already exists");
			}
			Table table = new Table(tableName, columns);
			ArrayList<Table> tables = new ArrayList<>();
			tables.add(table);
			Database db = new Database(dbname, tables);
			return pdm.updateDatabase(db);
		} else {
			throw new Exception("Table name or database name is invalid");
		}
	}

	/**
	 * Get all table name from database.
	 * 
	 * @param _databaseName
	 * @return ArrayList<String>
	 * @throws Exception
	 */
	public ArrayList<String> getAllTableName(String dbname) throws Exception {
		if (!dbname.isEmpty()) {
			// 1. Create database
			Database db = new Database(dbname);
			// 2. add database to configuration file

			Collection<Table> collection;
			try {
				collection = pdm.getAllTable(db);
				if (collection == null) {
					return null;
				}
			} catch (Exception e) {
				return null;
			}
			ArrayList<String> result = new ArrayList<String>();
			for (Object object : collection) {
				result.add(((Table) object).getName());
			}
			return result;
		} else {
			throw new Exception("Database name is null");
		}
	}

	/**
	 * Drop a table with dbname and table name
	 * 
	 * @param dbname
	 * @param tableName
	 * @return true if successful and vice versa.
	 * @throws Exception
	 */
	public boolean dropTable(String dbname, String tableName) throws Exception {
		if (dbname == null || tableName == null) {
			throw new Exception("Database name or tablename is null");
		}
		// 1. Create database and table with name
		Database db = new Database(dbname);
		Table table = new Table(tableName);
		return pdm.dropTable(db, table);
	}

	/**
	 * insert a row into table
	 * 
	 * @param tableName
	 *            string
	 * @param rowData
	 *            rowDTO
	 * @return boolean true if successful
	 * @author JML
	 * @throws Exception
	 */
	public boolean insertRow(String dbName, String tableName, RowDTO rowData)
			throws Exception {
		DatabaseData db = pdm.getDatabaseData(dbName);
		TableData table = db.getTableData(tableName);
		if (table != null) {
			table.addRow(rowData);

			// remove table data in database data if it's existing.
			db.removeTableData(table);
			// add table data to database data.
			db.addTableData(table);

			pdm.updateDatabaseData(db);
			return true;
		}
		throw new Exception("Database name or table name is invalid");
	}

	/**
	 * Insert rows to table
	 * 
	 * @param dbName
	 * @param tableName
	 * @param rows
	 * @return
	 * @throws Exception
	 * @author Stanley, hathao298
	 */
	public boolean insertRows(String dbName, String tableName,
			ArrayList<RowDTO> rows) throws Exception {
		// 1.
		DatabaseData db = pdm.getDatabaseData(dbName);
		TableData table = db.getTableData(tableName);
		if (table == null) {
			// new data to table
			table = new TableData(tableName);
		}

		for (RowDTO rowDTO : rows) {
			table.addRow(rowDTO);
		}
		// remove table data in database data if it's existing.
		db.removeTableData(table);
		// add table data to database data.
		db.addTableData(table);

		pdm.updateDatabaseData(db);
		return true;
	}

	/**
	 * Replace table data in database data if it's exists.
	 * 
	 * @param dbName
	 * @param tblName
	 * @param tblData
	 * @return
	 * @throws JsonIOException
	 * @throws JsonSyntaxException
	 * @throws IOException
	 */
	public boolean replaceTableData(String dbName, String tblName,
			TableData tblData) throws JsonIOException, JsonSyntaxException,
			IOException {
		// 1. Get database data
		DatabaseData db = pdm.getDatabaseData(dbName);

		// 2. Remove old table data
		db.removeTableData(tblData);
		// 3. Add new table data
		db.addTableData(tblData);

		// 4. Update table data
		pdm.updateDatabaseData(db);
		return true;
	}

	/**
	 * Select 200 rows from table data
	 * 
	 * @param tableName
	 *            string
	 * @return ArrayList<RowDTO>
	 * @author JML, Stanley
	 * @throws FileNotFoundException
	 * @throws JsonSyntaxException
	 * @throws JsonIOException
	 */
	public ArrayList<RowDTO> select200Rows(String dbName, String tableName)
			throws JsonIOException, JsonSyntaxException, FileNotFoundException {
		// 1. Get database data
		DatabaseData db = pdm.getDatabaseData(dbName);
		TableData tbl = new TableData(tableName);
		for (TableData tableData : db.getTableData()) {
			if (tableData.equals(tbl)) {
				ArrayList<RowDTO> result = new ArrayList<RowDTO>();
				if (tableData.getRows().size() <= 200) {
					result = tableData.getRows();
				} else {
					for (int i = 0; i < 200; i++) {
						result.add(tableData.getRows().get(i));
					}
				}
				return result;
			}
		}
		return null;
	}

	/**
	 * Get table data by database name and table name
	 * 
	 * @param dbName
	 * @param tblName
	 * @return TableData
	 * @throws JsonIOException
	 * @throws JsonSyntaxException
	 * @throws FileNotFoundException
	 */
	public TableData getTableData(String dbName, String tblName)
			throws JsonIOException, JsonSyntaxException, FileNotFoundException {
		return this.pdm.getTableData(dbName, tblName);
	}

	/**
	 * Get all columns of table
	 * 
	 * @param dbName
	 * @param tableName
	 * @return ArrayList<ColumnDTO>
	 * @throws Exception
	 */
	public ArrayList<ColumnDTO> getTableColumns(String dbName, String tableName)
			throws Exception {
		if (dbName != null && tableName != null) {
			Table tbl = new Table(tableName);
			Database db = new Database(dbName);
			Table table = pdm.getTable(db, tbl);
			if (table != null) {
				return table.getColumns();
			}
		} else {
			throw new Exception("Table name or database name is invalid");
		}
		return null;
	}

	/**
	 * Update table data with rows and theirs indexes
	 * 
	 * @param dbName
	 * @param tblName
	 * @param indexes
	 * @param rows
	 * @return true if successful and vice versa.
	 * @throws Exception
	 */
	public boolean updateTableData(String dbName, String tblName,
			ArrayList<Integer> indexes, ArrayList<RowDTO> rows)
			throws Exception {
		DatabaseData dbData = pdm.getDatabaseData(dbName);
		if (dbData != null) {
			TableData tblData = dbData.getTableData(tblName);
			if (tblData != null) {
				tblData.updateRows(indexes, rows);
			}
			return pdm.updateDatabaseData(dbData);
		} else {
			throw new Exception("Database " + dbName + " is not exists");
		}
	}

	/**
	 * Delete table data
	 * @param dbName
	 * @param tblName
	 * @param indexes
	 * @param rows
	 * @return true if successful and vice versa.
	 * @throws Exception 
	 */
	public boolean deleteTableData(String dbName, String tblName,
			ArrayList<Integer> indexes) throws Exception {
		DatabaseData dbData = pdm.getDatabaseData(dbName);
		if (dbData != null) {
			TableData tblData = dbData.getTableData(tblName);
			if (tblData != null) {
				tblData.deleteRows(indexes);
			}
			return pdm.updateDatabaseData(dbData);
		} else {
			throw new Exception("Database " + dbName + " is not exists");
		}
	}
}
